SELECT LEFT((areasymbol), 2) AS state
, l.areasymbol, l.areaname, mu.mukey, mu.musym, mu.nationalmusym, mu.muname, mukind, mu.muacres
, c.majcompflag, c.comppct_r, c.compname, c.cokey, compkind
, (mu.muacres*c.comppct_r/100) AS compacres, localphase, slope_l,
CASE WHEN slope_r IS NULL THEN (slope_l+slope_h/2)
ELSE slope_r END AS slope_r,
CASE WHEN slope_r = 0 THEN .1 ELSE slope_r END AS slope, slope_h, hydgrp,
CASE WHEN (100-sieveno10_r) = 0 THEN 1
ELSE (100-sieveno10_r) END AS sur_hor_frags,
(100-sieveno10_r) AS A_hor_frags , sieveno10_r , kffact , kwfact
, (SELECT CASE
WHEN hydgrp = 'A' THEN 8
WHEN hydgrp = 'B' THEN 6
WHEN hydgrp = 'C' THEN 4
WHEN hydgrp = 'D' THEN 2
WHEN hydgrp = 'A/D' THEN 8
WHEN hydgrp = 'B/D' THEN 6
WHEN hydgrp = 'C/D' THEN 4
ELSE 1
END) AS svi_leaching
, (SELECT CASE
WHEN hydgrp = 'A' THEN 2
WHEN hydgrp = 'B' THEN 4
WHEN hydgrp = 'C' THEN 6
WHEN hydgrp = 'D' THEN 8
WHEN hydgrp = 'A/D' THEN 2
WHEN hydgrp = 'B/D' THEN 4
WHEN hydgrp = 'C/D' THEN 6
ELSE 1
END) AS svi_runoff
INTO #SVI
FROM legend AS l
INNER JOIN mapunit AS mu ON mu.lkey = l.lkey
INNER JOIN component AS c ON c.mukey = mu.mukey AND compkind NOT LIKE 'miscellaneous area' AND l.areasymbol LIKE
'IA001'
INNER JOIN chorizon ch ON ch.cokey = c.cokey AND hzdept_r = 0
ORDER BY l.areasymbol, mu.musym
SELECT
mukey, cokey
, (svi_runoff*slope*sur_hor_frags*kffact) AS SVI_runoff_Index , (SELECT CAST(CASE
WHEN hydgrp LIKE 'B%' AND slope >6 THEN 4
WHEN hydgrp LIKE 'C%' AND slope >6 THEN 4
WHEN hydgrp LIKE 'D%' AND slope >4 THEN 4
WHEN hydgrp LIKE 'B%' AND (slope >= 4 AND slope <= 6) AND kffact >= 0.32 THEN 3
WHEN hydgrp LIKE 'C%' AND (slope >= 2 AND slope <= 6) AND kffact >= 0.28 THEN 3
WHEN hydgrp LIKE 'D%' AND (slope >= 2 AND slope <= 4) THEN 3
WHEN hydgrp LIKE 'B%' AND (slope >= 4 AND slope <= 6) AND kffact < 0.32 THEN 2
WHEN hydgrp LIKE 'C%' AND (slope >= 2 AND slope <= 6) AND kffact < 0.28 THEN 2
WHEN hydgrp LIKE 'D%' AND slope >= 2 AND kffact >= 0.28 THEN 2
WHEN hydgrp LIKE 'D%' AND slope < 2 AND kffact < 0.28 THEN 2
WHEN hydgrp LIKE 'C%' AND slope < 2 THEN 1
WHEN hydgrp LIKE 'B%' AND slope < 4 THEN 1
WHEN hydgrp LIKE 'A%' THEN 1
ELSE 0 END AS real)) AS SVI_Runoff_B
, (svi_leaching*slope*sur_hor_frags*kffact) AS SVI_Leaching_Index , (SELECT CAST (CASE
WHEN hydgrp LIKE 'A%'and slope <= 12 THEN 4
WHEN hydgrp LIKE 'B%'and slope < 3 AND kffact < 0.24 THEN 4
WHEN hydgrp LIKE 'A%'and slope > 12 THEN 3
WHEN hydgrp LIKE 'B%'and (slope >= 3 AND slope <= 12) AND kffact < 0.24 THEN 3
WHEN hydgrp LIKE 'B%'and slope <= 12 AND kffact >= 0.24 THEN 2
WHEN hydgrp LIKE 'B%'and slope > 12 THEN 2
WHEN hydgrp LIKE 'C%' THEN 2
WHEN hydgrp LIKE 'D%' THEN 1
ELSE 0 END AS INT)) AS SVI_Leach_B
INTO #SVI1
FROM #SVI
SELECT
state, areasymbol, areaname, #SVI.mukey, musym, nationalmusym, muname, mukind, muacres, majcompflag
, #SVI.cokey, comppct_r, compname, compkind, compacres, localphase, slope_l, slope_r, slope_h
, hydgrp, svi_leaching, svi_runoff, slope, A_hor_frags, sieveno10_r, kffact, kwfact, SVI_Leaching_Index
, (SELECT CAST (CASE WHEN SVI_Leach_B = 1 AND sur_hor_frags >= 30 THEN 3
WHEN SVI_Leach_B = 2 AND sur_hor_frags >= 30 THEN 4
WHEN SVI_Leach_B = 3 AND sur_hor_frags >= 30 THEN 4
WHEN SVI_Leach_B = 1 AND (sur_hor_frags > 10 AND sur_hor_frags < 30) THEN 2
WHEN SVI_Leach_B = 2 AND (sur_hor_frags > 10 AND sur_hor_frags < 30) THEN 3
WHEN SVI_Leach_B = 3 AND (sur_hor_frags > 10 AND sur_hor_frags < 30) THEN 4
ELSE SVI_Leach_B END AS real)) AS Leach , SVI_Leach_B , SVI_runoff_Index , SVI_Runoff_B
FROM #SVI
INNER JOIN #SVI1 ON #SVI.cokey=#SVI1.cokey
ORDER BY areasymbol, musym
DROP TABLE #SVI1
DROP TABLE #SVI